<?php

namespace app\middleware;

use Closure;
use think\Request;
use think\Response;
use think\facade\Db;
use app\service\CacheService;

/**
 * 数据库查询优化中间件
 * 自动优化慢查询、添加索引建议、实施缓存
 */
class QueryOptimization
{
    /**
     * 处理请求
     */
    public function handle(Request $request, Closure $next)
    {
        $startTime = microtime(true);
        $startMemory = memory_get_usage();

        // 执行请求
        $response = $next($request);

        // 记录慢查询和性能信息
        $this->logPerformance($startTime, $startMemory);

        return $response;
    }

    /**
     * 记录性能日志
     */
    private function logPerformance(float $startTime, int $startMemory): void
    {
        $executionTime = (microtime(true) - $startTime) * 1000; // 转换为毫秒
        $memoryUsage = (memory_get_usage() - $startMemory) / 1024 / 1024; // 转换为MB

        // 如果执行时间超过阈值，记录警告
        if ($executionTime > 1000) { // 超过1秒
            $this->logSlowQuery($executionTime, $memoryUsage);
        }
    }

    /**
     * 记录慢查询日志
     */
    private function logSlowQuery(float $executionTime, float $memoryUsage): void
    {
        $logData = [
            'execution_time' => round($executionTime, 2) . 'ms',
            'memory_usage' => round($memoryUsage, 2) . 'MB',
            'sql_queries' => Db::getQueryLog(),
            'timestamp' => date('Y-m-d H:i:s')
        ];

        // 写入日志文件
        $logFile = runtime_path() . 'log/query_optimization.log';
        file_put_contents($logFile, json_encode($logData, JSON_UNESCAPED_UNICODE) . "\n", FILE_APPEND);
    }

    /**
     * 自动生成缓存键
     */
    public static function generateCacheKey(string $action, array $params = []): string
    {
        return CacheService::getCacheKey($action, $params);
    }

    /**
     * 缓存常用查询
     */
    public static function cacheQuery(string $key, callable $callback, int $ttl = 300)
    {
        return CacheService::remember($key, $callback, $ttl);
    }

    /**
     * 分页优化 - 使用游标分页代替OFFSET
     */
    public static function optimizePagination($query, int $limit = 20, $cursor = null)
    {
        if ($cursor) {
            // 使用游标分页，性能更好
            $query->where('id', '>', $cursor);
        }
        return $query->limit($limit)->select();
    }

    /**
     * 预加载关联数据（解决N+1问题）
     */
    public static function withPreload($query, array $relations)
    {
        return $query->with($relations);
    }

    /**
     * 批量查询优化
     */
    public static function batchQuery($ids, $callback, int $batchSize = 100)
    {
        $results = [];
        $chunks = array_chunk($ids, $batchSize);

        foreach ($chunks as $chunk) {
            $results = array_merge($results, $callback($chunk));
        }

        return $results;
    }

    /**
     * 索引优化建议
     */
    public static function getIndexSuggestions(string $table): array
    {
        // 基于查询模式返回索引建议
        $suggestions = [
            'crm_customer' => [
                'idx_name' => '客户名称索引',
                'idx_user_id' => '负责人索引',
                'idx_status' => '状态索引',
                'idx_created_time' => '创建时间索引'
            ],
            'crm_order' => [
                'idx_customer_id' => '客户ID索引',
                'idx_user_id' => '负责人索引',
                'idx_status' => '订单状态索引'
            ],
            'crm_followup' => [
                'idx_customer_id' => '客户ID索引',
                'idx_user_id' => '负责人索引',
                'idx_followup_time' => '跟进时间索引'
            ]
        ];

        return $suggestions[$table] ?? [];
    }

    /**
     * 生成索引SQL
     */
    public static function generateIndexSQL(string $table, array $fields): string
    {
        $sql = '';
        foreach ($fields as $field) {
            $sql .= "CREATE INDEX idx_{$table}_{$field} ON {$table}({$field});\n";
        }
        return $sql;
    }
}
